import tushare as ts
import pandas as pd
from sqlalchemy import create_engine
import datetime
import os
import numpy as np

import sys, time
# from colorama import Fore, Back, Style
import colorama
from colorama import init, Fore, Back, Style
init(autoreset=True)
# 连接数据库
engine_ts  = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/tdx?charset=utf8mb4",echo=False,max_overflow=5)
# //////////////////////////////////////////get_df///////////////////////////////////////////////////////////////////get_df
class clsImp_CSV_to_DB:
    bianma=""
    tableName=""
    tmpTag=""
    def __init__(self, bianma="gbk",tableName="stock_all_data", tmpTag="临时数据"):

        self.bianma=bianma
        self.tableName=tableName
        self.tmpTag=tmpTag
        info = "def __init__参数初始化(\t" + self.bianma + "\t" + self.tmpTag + "\tDB TABLE: " + self.tableName + ")"
        print(info)
    def get_data(self,path_file):
        print("导入路径：",path_file)
        pathDir = os.listdir(path_file)
        count = len(pathDir)

        print("\n原始文件路径：( " + path_file, ")", '文件数量', count,'\n\n')

        start = time.perf_counter()
        c = 0
        scale = count
        i = 1
        dbCount=0
        for file in pathDir:
            path = path_file + file  # 完整文件路径
            # print(path)
            df=self.get_df(path)
            cur_count =df.shape[0]
            dbCount+=cur_count
            # 进度条 [1]	17 % ▋▋▋▋▋▋▋▋......................................... 0.83s 	SZ#000651.txt
            a = (i / scale) * 100
            b = '.' * 50
            c = '▋' * (int((i * 50) / scale))
            b = '.' * (49 - int((i * 50) / scale))
            dur = time.perf_counter()  # //每次获取当前时间
            print("\r[{}]\033[1;31m\t{:^3.0f}% {}{} {:.2f}s".format(i, a, c, b, dur), '\t' + file,"当前：",cur_count,"总共：",dbCount, end=' ')
            # if i==10:
            #     break
            i+=1
        printinfo("\n\n数据库共INSERT 记录总共："+str(dbCount),"RED")
    def get_df(self,pathFile):
        # print("def get_df(self,pathFile,flag_day)")
        titleName = ['ts_code', 'trade_date', 'trade_times', 'open', 'high', 'low', 'close', 'vol', 'amount','symbol','name','k_type','adj','market','tag','insertDate']
        # df = pd.read_table(pathFile, header=1, encoding=self.bianma, names=titleName, converters={'trade_times': str})
        # df = pd.read_table(pathFile, encoding=self.bianma)
        df = pd.read_csv(pathFile, header=1,encoding=self.bianma, names=titleName)

        # *********************************导入到数据库**********************************
        if df.shape[0] > 1:
            df.to_sql(self.tableName,engine_ts, index=True, if_exists='append')
        # *********************************导入到数据库**********************************
        # 显示完整列的信息
        # pd.set_option('display.expand_frame_repr', False)
        # print(df.tail(5))
        return df
#////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////confirm
import re
def confirm(tablename):
    # print('tablename:'+tablename)
    sql = "show tables;"
    rs = engine_ts.execute(sql)  # 执行sql语句
    tables = [rs.fetchall()]  # 返回所有结果
    table_list = re.findall('(\'.*?\')', str(tables))
    table_list = [re.sub("'", '', each) for each in table_list]
    # print(table_list)
    if tablename in table_list:  # 存在
        return 1
    else:  # 不存在
        return 0
#///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

#////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////printinfo//////
def printinfo(text,colr="GREEN"):
    if colr=="CYAN":
        print(Fore.CYAN +Style.BRIGHT+ text)
    if colr=="RED":
        print(Fore.RED +Style.BRIGHT+ text)
    if colr=="BLUE":
        print(Fore.RED +Style.BRIGHT+ text)
    if colr=="GREEN":
        print(Fore.GREEN +Style.BRIGHT+ text)
    if colr=="WHITE":
        print(Fore.WHITE +Style.BRIGHT+ text)
    if colr=="YELLOW":
        print(Fore.YELLOW +Style.BRIGHT+ text)
    if colr=="MAGENTA":
        print(Fore.MAGENTA +Style.BRIGHT+ text)
# //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////__name__ == '__main__':
# Press the green button in the gutter to run the script.
# =============================    程序入口    =============================
if __name__ == '__main__':
    os.system('cls')
    printinfo("程序开始运行。。。")
    printinfo(" import 导入数据库。。。。")
    # 统计程序开始时间
    starttime = datetime.datetime.now()

    # engine_ts.execute("DELETE FROM stock_all_data")
    c_to_db =clsImp_CSV_to_DB("utf-8")
    # c_to_db.get_df("E:\\stcokData\\20220116\\export\\stock_all_qfq\\")
    c_to_db.get_data("E:\\stcokData\\20220116\\export\\stock_all_qfq\\")
    # END 程序结束
    # 统计程序开结束时间
    endtime = datetime.datetime.now()
    printinfo("\n程序运行时间：" + str(endtime - starttime))
    print('=============================\t程序运行结束\t=============================')